package com.buaa.dao;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FileUtils;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.junit.Test;

import com.buaa.domain.Country;
import com.buaa.domain.DetectionField;
import com.buaa.domain.IndustryType;
import com.buaa.domain.Institution;
import com.buaa.domain.InstitutionAtt;
import com.buaa.domain.InstitutionType;
import com.buaa.domain.Instrument;
import com.buaa.domain.InstrumentAtt;
import com.buaa.domain.LongjiangIndustry;
import com.buaa.domain.Project;
import com.buaa.domain.SubjectField;
import com.buaa.domain.Talent;
import com.buaa.domain.TechnicalField;
import com.buaa.domain.TradeType;
import com.buaa.util.BMapUtils;
import com.buaa.util.HibernateUtils;
import com.buaa.util.LimitQueue;
import com.buaa.util.etl.InstitutionUtils;
import com.buaa.util.etl.InstrumentUtils;
import com.buaa.util.etl.ResourceUtils;

public class DataImport {

	private String base_path = "D:/Code/bibased/data/";

	private String institution_path = base_path + "最新机构数据--含机构编码.xlsx";
	private String instrument_path = base_path + "大型仪器（对接）0105.xlsx";
	private String resource_path = "D:/3.xlsx";
	private String industry_type = base_path + "产业类别.xlsx";
	private String trade_type = base_path + "行业类别.xlsx";
	private String institution_type = base_path + "机构类型.xlsx";
	private String technical_field = base_path + "技术领域.xlsx";
	private String detection_field = base_path + "检测领域.xlsx";
	private String longjiang_industry = base_path + "龙江产业.xlsx";
	private String subject_field = base_path + "主要学科领域.xlsx";

	private String talent_field = base_path + "黑龙江法人单位高层次科技人员.xlsx";
	//private static String talent_field1 =  "D:/Code/bibased/data/法人单位高层次科技人员.xlsx";
	private String base_field = base_path + "研究实验基地概况.xlsx";

	/**
	 * 人才数据导入
	 * @throws IOException 
	 * 
	 * @throws Exception
	 */
	@Test
	public void TalentDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(talent_field);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		XSSFCell cell;
		Talent talent = null;
		Session session = null;
		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		
		session = HibernateUtils.getCurrentSession();
		session.beginTransaction();
		
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			
			talent = new Talent();
			// 省 16P
			cell = row.getCell(15);
			talent.setProvince(cell == null ? null : cell.getStringCellValue());

			
			// 隶属代码 1A
			// 隶属 2B
			// 上级行政主管部分代码 3C
			cell = row.getCell(2);
			talent.setDepart_code(cell == null ? null : cell.getStringCellValue());
			// 上级行政主管部门 4D
			cell = row.getCell(3);
			talent.setDepart_name(cell == null ? null : cell.getStringCellValue());
			// 上一级主管单位代码 5E
			cell = row.getCell(4);
			talent.setHigher_unit_code(cell == null ? null : cell.getStringCellValue());
			// 上一级主管单位 6F
			cell = row.getCell(5);
			talent.setHigher_unit_name(cell == null ? null : cell.getStringCellValue());
			// 单位代码 7G
			cell = row.getCell(6);
			talent.setUnit_code(cell == null ? null : cell.getStringCellValue());
			// 单位名称 8H
			cell = row.getCell(7);
			talent.setUnit_name(cell == null ? null : cell.getStringCellValue());
			// 法人性质代码 9I
			cell = row.getCell(8);
			talent.setCor_code(cell == null ? null : cell.getStringCellValue());
			// 法人性质 10J
			cell = row.getCell(9);
			talent.setCor_nature(cell == null ? null : cell.getStringCellValue());
			// 单位属性代码 11K
			cell = row.getCell(10);
			talent.setUnit_att_code(cell == null ? null : cell.getStringCellValue());
			// 单位属性 12L
			cell = row.getCell(11);
			talent.setUnit_att(cell == null ? null : cell.getStringCellValue());
			// 是否属于科研机构转制型企业 13M
			cell = row.getCell(12);
			talent.setIs_trans(cell == null ? null : cell.getStringCellValue());
			// 所在区域 14N
			cell = row.getCell(13);
			talent.setArea_name(cell == null ? null : cell.getStringCellValue());
			// 所在区域编码 15O
			cell = row.getCell(14);
			talent.setArea_code(cell == null ? null : cell.getStringCellValue());
			
			// 市 17Q
			cell = row.getCell(16);
			talent.setCity(cell == null ? null : cell.getStringCellValue());
			// 区县 18R
			cell = row.getCell(17);
			talent.setCounty(cell == null ? null : cell.getStringCellValue());
			// 人员代码 19S
			cell = row.getCell(18);
			talent.setCode(cell == null ? null : cell.getStringCellValue());
			// 姓名 20T
			cell = row.getCell(19);
			talent.setName(cell == null ? null : cell.getStringCellValue());
			// 性别 21U
			cell = row.getCell(20);
			talent.setSex(cell == null ? null : cell.getStringCellValue());
			// 国别代码 22V
			cell = row.getCell(21);
			talent.setCountry_code(cell == null ? null : cell.getStringCellValue());
			// 国别 23W
			cell = row.getCell(22);
			talent.setCountry(cell == null ? null : cell.getStringCellValue());
			// 证件类别代码 24X
			cell = row.getCell(23);
			talent.setId_code(cell == null ? null : cell.getStringCellValue());
			// 证件类别 25Y
			cell = row.getCell(24);
			talent.setId_type(cell == null ? null : cell.getStringCellValue());
			// 身份证号码 26Z
			cell = row.getCell(25);
			talent.setId_card(cell == null ? null : cell.getStringCellValue());
			// 政治面貌代码 27AA
			cell = row.getCell(26);
			talent.setPolitic_code(cell == null ? null : cell.getStringCellValue());
			// 政治面貌 28AB
			cell = row.getCell(27);
			talent.setPolitic(cell == null ? null : cell.getStringCellValue());
			// 职务 29AC
			cell = row.getCell(28);
			talent.setPost(cell == null ? null : cell.getStringCellValue());
			// 行政级别代码 30AD
			cell = row.getCell(29);
			talent.setAdministrative_code(cell == null ? null : cell.getStringCellValue());
			// 行政级别 31AE
			cell = row.getCell(30);
			talent.setAdministrative_level(cell == null ? null : cell.getStringCellValue());
			// 职称代码 32AF
			cell = row.getCell(31);
			talent.setTitile_code(cell == null ? null : cell.getStringCellValue());
			// 职称大类 33AG
			cell = row.getCell(32);
			talent.setTitle_type_big(cell == null ? null : cell.getStringCellValue());
			// 职称小类 34AH
			cell = row.getCell(33);
			talent.setTitle_type_small(cell == null ? null : cell.getStringCellValue());
			// 职称 35AI
			cell = row.getCell(34);
			talent.setTitle(cell == null ? null : cell.getStringCellValue());
			// 学历代码 36AJ
			cell = row.getCell(35);
			talent.setEdu_code(cell == null ? null : cell.getStringCellValue());
			// 学历 37AK
			cell = row.getCell(36);
			talent.setEdu(cell == null ? null : cell.getStringCellValue());
			// 学位代码 38AL
			cell = row.getCell(37);
			talent.setAcademic_code(cell == null ? null : cell.getStringCellValue());
			// 学位 39AM
			cell = row.getCell(38);
			talent.setAcademic(cell == null ? null : cell.getStringCellValue());
			// 在岗情况 40AN
			cell = row.getCell(39);
			talent.setGuard_code(cell == null ? null : cell.getStringCellValue());
			// 在岗情况 41AO
			cell = row.getCell(40);
			talent.setGuard(cell == null ? null : cell.getStringCellValue());
			// 工作单位 42AP
			// 单位内工作部门 43AQ
			cell = row.getCell(42);
			talent.setDepart(cell == null ? null : cell.getStringCellValue());
			// 是否属于研究实验基地 44AR
			cell = row.getCell(43);
			talent.setIs_in_base(cell == null ? null : cell.getStringCellValue());
			// 所属研究实验基地代码 45AS
			cell = row.getCell(44);
			talent.setBase_code(cell == null ? null : cell.getStringCellValue());
			// 所属研究实验基地 46AT
			cell = row.getCell(45);
			talent.setBase_name(cell == null ? null : cell.getStringCellValue());
			// 专家称号代码 47AU
			cell = row.getCell(46);
			talent.setExpert_title_code(cell == null ? null : cell.getStringCellValue());
			// 专家称号 48AV
			cell = row.getCell(47);
			talent.setExpert_title(cell == null ? null : cell.getStringCellValue());
			// 国外专家称号 49AW
			cell = row.getCell(48);
			talent.setForeign_expert_title(cell == null ? null : cell.getStringCellValue());
			// 其他专家称号 50AX
			cell = row.getCell(49);
			talent.setOther_expert_title(cell == null ? null : cell.getStringCellValue());
			// 导师情况代码 51AY
			cell = row.getCell(50);
			talent.setTutor_code(cell == null ? null : cell.getStringCellValue());
			// 导师情况 52AZ
			cell = row.getCell(51);
			talent.setTutor(cell == null ? null : cell.getStringCellValue());
			// 主要学科领域代码 53BA
			cell = row.getCell(52);
			talent.setSubject_code(cell == null ? null : cell.getStringCellValue());
			// 主要学科领域 54BB
			cell = row.getCell(53);
			talent.setSubject_field(cell == null ? null : cell.getStringCellValue());
			// 学术专长 55BC
			cell = row.getCell(54);
			talent.setSpecialty(cell == null ? null : cell.getStringCellValue());

			// 存到mysql数据库中
			
//			session = HibernateUtils.getCurrentSession();
//			session.beginTransaction();
			session.save(talent);
//			session.getTransaction().commit();
		}
		session.getTransaction().commit();
		wb.close();
		System.out.println("complete!");
	}

	/**
	 * 机构数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void InstitutionDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(institution_path);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		XSSFCell cell;
		Institution institution;
		InstitutionAtt institutionAtt;
		Session session = null;
		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;

		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			System.out.println(cur);
			if (cur == 1 || (cur >= 1809 && cur <= 1812)) {
				continue;
			}

			institution = new Institution();
			institutionAtt = new InstitutionAtt();
			// 机构编码(1)
			cell = row.getCell(0);
			long code = InstitutionUtils.getCode(cell == null ? null : cell.getStringCellValue());
			institution.setCode(code);
			institutionAtt.setCode(code);

			// 是否加盟(2)
			cell = row.getCell(1);
			institution.setKind(cell == null ? "未定义" : cell.getStringCellValue());

			// 单位名称(3)
			cell = row.getCell(2);
			institution.setName(InstitutionUtils.getName(cell == null ? null : cell.getStringCellValue()));

			// 机构类型(4)
			cell = row.getCell(3);
			institution.setType(InstitutionUtils.getType(cell == null ? null : cell.getStringCellValue()));

			// 机构地址(5)
			// cell = row.getCell(4);
			// institution.setAddr(InstitutionUtils.getAddr(cell == null ? null
			// : cell.getStringCellValue()));

			// 所在省份(6)
			cell = row.getCell(5);
			String province = InstitutionUtils.getProvince(cell == null ? null : cell.getStringCellValue());
			institution.setProvince(province);

			// 所在地市(7)
			cell = row.getCell(6);
			String city = InstitutionUtils.getCity(cell == null ? null : cell.getStringCellValue());
			institution.setCity(city);

			// 所在区县(8)
			cell = row.getCell(7);
			String county = InstitutionUtils.getCountry(cell == null ? null : cell.getStringCellValue());
			institution.setCounty(county);

			// 所在街道(9)
			cell = row.getCell(8);
			String street = InstitutionUtils.getStreet(cell == null ? null : cell.getStringCellValue());
			institution.setStreet(street);

			// 机构地址(5)
			String addr = province + city + county + street;
			institution.setAddr(addr);

			// 上级主管部门(10)
			cell = row.getCell(9);
			institution.setDepart(InstitutionUtils.getDepart(cell == null ? null : cell.getStringCellValue()));

			// 法人姓名(11)
			cell = row.getCell(10);
			institution.setCor_name(InstitutionUtils.getCor_name(cell == null ? null : cell.getStringCellValue()));

			// 法人职务(12)
			cell = row.getCell(11);
			institution.setCor_job(InstitutionUtils.getJob(cell == null ? null : cell.getStringCellValue()));

			// 联系人姓名(13)
			cell = row.getCell(12);
			institution.setContact_name(InstitutionUtils.getCor_name(cell == null ? null : cell.getStringCellValue()));

			// 联系人电话(14)
			cell = row.getCell(13);
			institution.setContact_phone(InstitutionUtils.getPhone(cell == null ? null : cell.getStringCellValue()));

			// 联系人职务(15)
			cell = row.getCell(14);
			institution.setContact_job(InstitutionUtils.getJob(cell == null ? null : cell.getStringCellValue()));

			// 单位网址(16)
			cell = row.getCell(15);
			institution.setUrl(InstitutionUtils.getUrl(cell == null ? null : cell.getStringCellValue()));

			// 单位简介(17)
			cell = row.getCell(16);
			institutionAtt.setDescription(InstitutionUtils.getDesc(cell == null ? null : cell.getStringCellValue()));

			// 技术领域（父）(18)
			cell = row.getCell(17);
			institution.setTech_parent(InstitutionUtils.getTech(cell == null ? "未定义" : cell.getStringCellValue()));

			// 技术领域（子）(19)
			cell = row.getCell(18);
			institution.setTech_child(InstitutionUtils.getTech(cell == null ? "未定义" : cell.getStringCellValue()));

			// 成立时间(20)
			cell = row.getCell(19);
			institution.setBirth(InstitutionUtils.getBirth(cell == null ? null : cell.getStringCellValue()));

			// 注册资金(21)
			cell = row.getCell(20);
			institution.setReg_money(InstitutionUtils.getReg_money(cell == null ? null : cell.getStringCellValue()));

			// 单位图片(22)
			cell = row.getCell(21);
			institution.setPic(InstitutionUtils.getPic(cell == null ? null : cell.getStringCellValue()));

			// 经度、纬度
			Map<String, Float> location = BMapUtils.getLatAndLngByAddress(addr);
			institution.setLng(location.get("lng"));
			institution.setLat(location.get("lat"));

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(institution);
			session.save(institutionAtt);
			session.getTransaction().commit();
		}
		wb.close();
		System.out.println("complete!");
	}

	/**
	 * 设备数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void InstrumentDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(instrument_path);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		XSSFCell cell;
		Instrument instrument;
		InstrumentAtt instrumentAtt;
		Session session = null;
		LimitQueue<String> limitQueue = new LimitQueue<String>(10);
		Map<String, Map<String, Float>> map = new HashMap<String, Map<String, Float>>();
		Map<String, Float> location;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			instrument = new Instrument();
			instrumentAtt = new InstrumentAtt();
			// id : 对应于excel中的行数为id
			// 单位名称（1）
			cell = row.getCell(0);
			instrument.setUnit_name(InstrumentUtils.getUnit_name(cell == null ? null : cell.getStringCellValue()));

			// 单位编码（2）
			cell = row.getCell(1);
			instrument.setUnit_code(InstrumentUtils.getUnit_code(cell == null ? null : cell.getStringCellValue()));

			// 类型（3）
			cell = row.getCell(2);
			instrument.setType(InstrumentUtils.getType(cell == null ? null : cell.getStringCellValue()));

			// 型号（4）
			cell = row.getCell(3);
			instrument.setModel(InstrumentUtils.getModel(cell == null ? null : cell.getStringCellValue()));

			// 购置时间（5）
			cell = row.getCell(4);
			instrument.setBuy_time(InstrumentUtils.getBuy_time(cell == null ? null : cell.getStringCellValue()));

			// 原值金额（6）
			cell = row.getCell(5);
			instrument.setOrg_value(InstrumentUtils.getOrg_value(cell == null ? null : cell.getStringCellValue()));

			// 币种(7)
			cell = row.getCell(6);
			instrument.setCurrency(InstrumentUtils.getCurrency(cell == null ? null : cell.getStringCellValue()));

			// 外币折合成当时的人民币值（8）
			cell = row.getCell(7);
			instrument.setRmb_value(InstrumentUtils.getRmb_value(cell == null ? null : cell.getStringCellValue()));

			// 主要技术指标（9）
			cell = row.getCell(8);
			instrumentAtt.setQualification(
					InstrumentUtils.getQualification(cell == null ? null : cell.getStringCellValue()));

			// 主要附件（10）
			cell = row.getCell(9);
			instrumentAtt.setAccessory(InstrumentUtils.getAccessory(cell == null ? null : cell.getStringCellValue()));

			// 功能/应用范围(11)
			cell = row.getCell(10);
			instrumentAtt.setFunction(InstrumentUtils.getFunction(cell == null ? null : cell.getStringCellValue()));

			// 接待时间（工作日）(12)
			cell = row.getCell(11);
			instrument.setWeekday(InstrumentUtils.getWeekday(cell == null ? null : cell.getStringCellValue()));

			// 仪器所在部门(13)
			cell = row.getCell(12);
			instrument.setDepart_name(InstrumentUtils.getDepart_name(cell == null ? null : cell.getStringCellValue()));

			// 仪器设施联系人姓名(14)
			cell = row.getCell(13);
			instrument
					.setContact_name(InstrumentUtils.getContact_name(cell == null ? null : cell.getStringCellValue()));

			// 仪器设备联系人电话(15)
			cell = row.getCell(14);
			instrument.setContact_phone(
					InstrumentUtils.getContact_phone(cell == null ? null : cell.getStringCellValue()));

			// 仪器图片（16）
			cell = row.getCell(15);
			instrument.setPic(InstrumentUtils.getPic(cell == null ? null : cell.getStringCellValue()));

			// 仪器中文名称(17)
			cell = row.getCell(16);
			instrument.setName_zh(InstrumentUtils.getName_zh(cell == null ? null : cell.getStringCellValue()));

			// 生产国家(18)
			cell = row.getCell(17);
			instrument.setHome(InstrumentUtils.getHome(cell == null ? null : cell.getStringCellValue()));

			// 仪器使用状态(19)
			cell = row.getCell(18);
			instrument.setStatus(InstrumentUtils.getStatus(cell == null ? null : cell.getStringCellValue()));

			// 资产管理编号(20)
			cell = row.getCell(19);
			instrument.setManage_code(InstrumentUtils.getManage_code(cell == null ? null : cell.getStringCellValue()));

			// 仪器英文名称(21)
			cell = row.getCell(20);
			instrument.setName_en(InstrumentUtils.getName_en(cell == null ? null : cell.getStringCellValue()));

			// 自筹（22）
			cell = row.getCell(21);
			instrument.setRaise(InstrumentUtils.getRaise(cell == null ? null : cell.getStringCellValue()));

			// 仪器分类编码(23)
			cell = row.getCell(22);
			instrument.setType_code(InstrumentUtils.getType_code(cell == null ? null : cell.getStringCellValue()));

			// 制造商(24)
			cell = row.getCell(23);
			instrument
					.setManufacturer(InstrumentUtils.getManufacturer(cell == null ? null : cell.getStringCellValue()));

			// 其他(25)
			cell = row.getCell(24);
			instrument.setOther(InstrumentUtils.getOther(cell == null ? null : cell.getStringCellValue()));

			// 仪器所在地区（市）(26)
			cell = row.getCell(25);
			String city = InstrumentUtils.getCity(cell == null ? null : cell.getStringCellValue());
			instrument.setCity(city);

			// 所在研发基地类型（27）
			cell = row.getCell(26);
			instrument.setBase_type(InstrumentUtils.getBase_type(cell == null ? null : cell.getStringCellValue()));

			// 所在研发基地名称（28）
			cell = row.getCell(27);
			instrument.setBase_name(InstrumentUtils.getBase_name(cell == null ? null : cell.getStringCellValue()));

			// 仪器设备联系人传真(29)
			cell = row.getCell(28);
			instrument.setContact_fax(InstrumentUtils.getContact_fax(cell == null ? null : cell.getStringCellValue()));

			// 仪器设备联系人email(30)
			cell = row.getCell(29);
			instrument.setContact_email(
					InstrumentUtils.getContact_email(cell == null ? null : cell.getStringCellValue()));

			// 仪器设备联系人通讯地址(31)
			cell = row.getCell(30);
			String addr = InstrumentUtils.getContact_addr(cell == null ? null : cell.getStringCellValue());
			if (!addr.contains("市") && !addr.contains("地区") && !addr.equals("")) {
				if (!city.equals("") && !city.equals("其他")) {
					addr = addr.substring(0, 4) + city + addr.substring(4);
				}
			}
			instrument.setContact_addr(addr);

			// 仪器设备联系人邮编(32)
			cell = row.getCell(31);
			instrument.setContact_zipcode(
					InstrumentUtils.getContact_zipcode(cell == null ? null : cell.getStringCellValue()));

			// 是否进口（33）
			cell = row.getCell(32);
			instrument.setIs_import(InstrumentUtils.getIs_import(cell == null ? null : cell.getStringCellValue()));

			// 服务行业类别(34)
			cell = row.getCell(33);
			instrument.setIndustry_type(
					InstrumentUtils.getIndustry_type(cell == null ? null : cell.getStringCellValue()));

			// 主要学科领域 (35)
			cell = row.getCell(34);
			instrument.setSubject(InstrumentUtils.getSubject(cell == null ? null : cell.getStringCellValue()));

			// 仪器所在地区（省）(36)
			cell = row.getCell(35);
			instrument.setProvince(InstrumentUtils.getProvince(cell == null ? null : cell.getStringCellValue()));

			// 仪器服务内容(37)
			cell = row.getCell(36);
			instrumentAtt.setService_content(
					InstrumentUtils.getService_content(cell == null ? null : cell.getStringCellValue()));

			// 经度、纬度
			if (limitQueue.contains(addr)) {
				location = map.get(addr);
				// 更新addr在队列中的顺序
				limitQueue.remove(addr);
				limitQueue.push(addr);
			} else {
				location = BMapUtils.getLatAndLngByAddress(addr);
				String key = limitQueue.push(addr);
				if (key != null)
					map.remove(key);
				map.put(addr, location);
			}
			instrument.setLng(location.get("lng"));
			instrument.setLat(location.get("lat"));

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(instrument);
			session.save(instrumentAtt);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 检测接口数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void ResourceDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(resource_path);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		XSSFCell cell = null;
		String data;
		Session session = null;
		int cur = 0;
		LimitQueue<String> limitQueue = new LimitQueue<String>(10);
		Map<String, Map<String, Float>> map = new HashMap<String, Map<String, Float>>();
		Map<String, Float> location;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			Project resource = new Project();

			// 单位名称(1)
			cell = row.getCell(0);
			if (cell != null) {
				data = cell.getStringCellValue();
				resource.setUnit_name(ResourceUtils.getUnit_name(data));
			}

			// 单位编码(2)
			cell = row.getCell(1);
			if (cell != null) {
				data = cell.getRawValue();
				resource.setUnit_code(ResourceUtils.getUnit_code(data));
			}

			// 项目名称/资源名称(3)-- 冗余
			// 产品/产品类别(4)
			cell = row.getCell(3);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setType(ResourceUtils.getType(data));
				}
			} else {
				resource.setType("");
			}

			// 限制范围_备注(5)
			cell = row.getCell(4);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setBound(ResourceUtils.getBound(data));
				}
			} else {
				resource.setBound("");
			}

			// 产业(6)
			cell = row.getCell(5);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setIndustry1(ResourceUtils.getIndustry1(data));
				}
			} else {
				resource.setIndustry1("");
			}

			// 检测领域(父)(7)
			cell = row.getCell(6);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setDf_parent(ResourceUtils.getDf_parent(data));
				}
			} else {
				resource.setDf_parent("");
			}

			// 行业类别（父）(8)
			cell = row.getCell(7);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setIc_parent(ResourceUtils.getIc_parent(data));
				}
			} else {
				resource.setIc_parent("");
			}

			// 黑龙江产业(9)
			cell = row.getCell(8);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setIndustry2(ResourceUtils.getIndustry2(data));
				}
			} else {
				resource.setIndustry2("");
			}

			// 检测价格(10)
			cell = row.getCell(9);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
					data = cell.getRawValue();
					resource.setDetection_price(ResourceUtils.getDetection_price(data));
				}
			} else {
				resource.setDetection_price(-1);
			}

			// 检测标准(11)
			cell = row.getCell(10);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setDetection_criterion(ResourceUtils.getDetection_criterion(data));
				}
			} else {
				resource.setDetection_criterion("");
			}

			// 检测周期(12)
			cell = row.getCell(11);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setDetection_period(ResourceUtils.getDetection_period(data));
				} else {
					data = cell.getRawValue();
					resource.setDetection_period(ResourceUtils.getDetection_period(data));
				}
			} else {
				resource.setDetection_period("");
			}

			// 服务图片(13)
			cell = row.getCell(12);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setPic(ResourceUtils.getPic(data));
				}
			} else {
				resource.setPic("");
			}

			// 委托书 (14)
			cell = row.getCell(13);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setProxy(ResourceUtils.getProxy(data));
				}
			} else {
				resource.setProxy("");
			}

			// 新增时间 (15)
			cell = row.getCell(14);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
					if (DateUtil.isCellDateFormatted(cell)) {
						resource.setCreate_time(cell.getDateCellValue());
					}
				}
			} else {
				resource.setCreate_time(new SimpleDateFormat("yyyy-MM-dd").parse("1000-01-01"));
			}

			// 修改时间 (16)
			cell = row.getCell(15);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
					if (DateUtil.isCellDateFormatted(cell)) {
						resource.setUpdate_time(cell.getDateCellValue());
					}
				}
			} else {
				resource.setUpdate_time(new SimpleDateFormat("yyyy-MM-dd").parse("1000-01-01"));
			}

			// 联系人(17)
			cell = row.getCell(16);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setContact_phone(ResourceUtils.getContact_person(data));
				}
			} else {
				resource.setContact_phone("");
			}

			// 联系人电话(18)
			cell = row.getCell(17);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setContact_phone(ResourceUtils.getContact_phone(data));
				}
			} else {
				resource.setContact_phone("");
			}

			// 联系人传真(19)
			cell = row.getCell(18);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setContact_fax(ResourceUtils.getContact_fax(data));
				}
			} else {
				resource.setContact_fax("");
			}

			// 联系人邮箱(20)
			cell = row.getCell(19);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setContact_email(ResourceUtils.getContact_email(data));
				}
			} else {
				resource.setContact_email("");
			}

			// 产品/产品类别(21)--冗余
			// 单位地址(22)
			cell = row.getCell(21);
			String addr = "";
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					addr = ResourceUtils.getUnit_addr(data);
					resource.setUnit_addr(addr);
				}
			} else {
				resource.setUnit_addr("");
			}

			// 单位所在城市(23)
			cell = row.getCell(22);
			String city = "";
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					city = ResourceUtils.getUnit_city(data);
					resource.setUnit_city(city);
				}
			} else {
				resource.setUnit_city("");
			}

			// 单位名称(24)--冗余
			// 检测领域(子)(25)
			cell = row.getCell(24);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setDf_child(ResourceUtils.getDf_child(data));
				}
			} else {
				resource.setDf_child("");
			}

			// 行业类别（子）(26)
			cell = row.getCell(25);
			if (cell != null) {
				if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
					data = cell.getStringCellValue();
					resource.setIc_child(ResourceUtils.getIc_child(data));
				}
			} else {
				resource.setIc_child("");
			}

			// 经度、纬度
			if (limitQueue.contains(addr)) {
				location = map.get(addr);
				// 更新addr在队列中的顺序
				limitQueue.remove(addr);
				limitQueue.push(addr);
			} else {
				location = BMapUtils.getLatAndLngByAddress(addr);
				String key = limitQueue.push(addr);
				if (key != null)
					map.remove(key);
				map.put(addr, location);
			}
			resource.setLng(location.get("lng"));
			resource.setLat(location.get("lat"));

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(resource);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	private String formatSumTime(long sumtime) {
		sumtime /= 1000;
		long minute = sumtime / 60;
		long second = sumtime % 60;
		return minute + "分" + second + "秒";
	}

	@SuppressWarnings("unchecked")
	@Test
	public void LBSInstrumentDataExport() throws IOException {

		List<Instrument> list = HibernateUtils.findByHQL("select new Instrument(id,name_zh,lng,lat) from Instrument",
				null);
		File file = new File("D://newdata.csv");
		StringBuilder fileContent = new StringBuilder();
		fileContent.append("title,address,longitude,latitude,coord_type,,instrument_name,instrument_id\n");
		for (Instrument instrument : list) {
			fileContent.append("仪器,," + instrument.getLng() + "," + instrument.getLat() + ",1,,"
					+ instrument.getName_zh() + "," + instrument.getId() + "\n");
		}
		FileUtils.writeStringToFile(file, fileContent.toString(), "UTF-8");
	}

	/**
	 * 城市数据导入
	 */
	@Test
	public void CityImport() {
		Session session = null;
		for (int i = 1; i <= 13; i++) {
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			// String sql = "insert into county(county_name,city_id) values(\""
			// + county + "\"," + 4 + ")";
			// System.out.println(sql);
			// session.createSQLQuery(sql).executeUpdate();;
			// session.getTransaction().commit();
		}
	}

	/**
	 * 区县数据导入
	 * 
	 * http://www.hlj.gov.cn/sq/qhrk/
	 * 全省现辖1个副省级城市，有12个地级市、1个地区行署，128个县(市、区)，省会哈尔滨市。
	 */
	@Test
	public void CountyImport() {
		Session session = null;
		// 哈尔滨 18
		// String city =
		// "道里区、南岗区、道外区、香坊区、平房区、呼兰区、松北区、阿城区、双城区、依兰县、方正县、宾县、巴彦县、木兰县、通河县、延寿县、尚志市、五常市";
		// 齐齐哈尔市 16(34)
		// String
		// city="龙沙区、建华区、铁锋区、昂昂溪区、富拉尔基区、碾子山、梅里斯达斡尔族区、龙江县、依安县、泰来县、甘南县、富裕县、克山县、克东县、拜泉县、讷河市";
		// 鸡西市9(43)
		// String city="鸡冠区、恒山区、滴道区、梨树区、城子河区、麻山区、鸡东县、虎林市、密山市";
		// 鹤岗市8(51)
		// String city = "向阳区、工农区、南山区、兴安区、东山区、兴山区、萝北县、绥滨县";
		// 双鸭山市8(59)
		// String city = "尖山区、岭东区、四方台区、宝山区、集贤县、友谊县、宝清县、饶河县";
		// 大庆市9(68)
		// String city = "萨尔图区、龙凤区、让胡路区、红岗区、大同区、肇州县、肇源县、林甸县、杜尔伯特蒙古族自治县";
		// 伊春市17(85)
		// String city =
		// "伊春区、南岔区、友好区、西林区、翠峦区、新青区、美溪区、金山屯区、五营区、乌马河区、汤旺河区、带岭区、乌伊岭区、红星区、上甘岭区、嘉荫县、铁力市";
		// 佳木斯市10(95)
		// String city = "向阳区、前进区、东风区、郊区、桦南县、桦川县、汤原县、抚远市、同江市、富锦市";
		// 七台河市4(99)
		// String city = "新兴区、桃山区、茄子河区、勃利县";
		// 牡丹江市10(109)
		// String city = "东安区、阳明区、爱民区、西安区、东宁县、林口县、绥芬河市、海林市、宁安市、穆棱市";
		// 黑河市6(115)
		// String city = "爱辉区、嫩江县、逊克县、孙吴县、北安市、五大连池市";
		// 绥化市10(125)
		// String city = "北林区、望奎县、兰西县、青冈县、庆安县、明水县、绥棱县、安达市、肇东市、海伦市";
		// 大兴安岭地区3(128)
		String city = "呼玛县、塔河县、漠河县";

		String[] counties = city.split("、");

		System.out.println(counties.length);
		for (String county : counties) {
			System.out.println(county);

			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			String sql = "insert into county(county_name,city_id) values(\"" + county + "\"," + 13 + ")";
			System.out.println(sql);
			session.createSQLQuery(sql).executeUpdate();
			session.getTransaction().commit();
		}
	}

	/**
	 * 每个县（区）坐标定位
	 */
	@SuppressWarnings("unchecked")
	@Test
	public void CountyCoordinateImport() {
		Session session = null;

		session = HibernateUtils.getCurrentSession();
		session.beginTransaction();
		String sql = "SELECT county_id,city_name,county_name FROM city,county WHERE city.city_id = county.city_id";
		System.out.println(sql);
		SQLQuery query = session.createSQLQuery(sql);
		List<Object[]> result = query.list();
		// System.out.println(result.get(0)[0]+"\t"+result.get(0)[1]);
		session.getTransaction().commit();

		Map<String, Float> location;
		for (Object[] objects : result) {
			location = BMapUtils.getLatAndLngByAddress("黑龙江省" + objects[1] + objects[2]);
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			sql = "UPDATE county SET lng = " + location.get("lng") + ",lat =" + location.get("lat")
					+ " WHERE county_id = " + objects[0];
			System.out.println(sql);
			session.createSQLQuery(sql).executeUpdate();
			session.getTransaction().commit();
		}
	}

	/**
	 * 产业类别数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void IndustryTypeDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(industry_type);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		IndustryType industryType;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			industryType = new IndustryType();

			// 1. 类型
			industryType.setType(row.getCell(0).toString());

			// 2. 代码
			industryType.setCode(row.getCell(1).toString());

			// 3. 名称
			industryType.setName(row.getCell(2).toString());

			// 4. 上级代码
			industryType.setParent_code(row.getCell(3) == null ? "" : row.getCell(3).toString());

			// 5. 级别
			industryType.setLevel(Double.valueOf(row.getCell(4).toString()).intValue());

			System.out.println(industryType);

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(industryType);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 行业类别数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void TradeTypeDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(trade_type);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		TradeType tradeType;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			tradeType = new TradeType();

			// 1. 类型
			tradeType.setType(row.getCell(0).toString());

			// 2. 代码
			tradeType.setCode(row.getCell(1).toString());

			// 3. 名称
			tradeType.setName(row.getCell(2).toString());

			// 4. 上级代码
			tradeType.setParent_code(row.getCell(3) == null ? "" : row.getCell(3).toString());

			// 5. 级别
			tradeType.setLevel(Double.valueOf(row.getCell(4).toString()).intValue());

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(tradeType);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 机构类型数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void InstitutionTypeDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(institution_type);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		InstitutionType institutionType;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			institutionType = new InstitutionType();

			// 1. 代码
			institutionType.setCode(row.getCell(0).toString());

			// 2. 名称
			institutionType.setName(row.getCell(1).toString());

			// 3. 上级代码
			institutionType.setParent_code(row.getCell(2) == null ? "" : row.getCell(2).toString());

			// 4. 级别
			institutionType.setLevel(Double.valueOf(row.getCell(3).toString()).intValue());

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(institutionType);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 技术领域数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void TechnicalFieldDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(technical_field);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		TechnicalField technicalField;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			technicalField = new TechnicalField();

			// 1. 代码
			technicalField.setCode(row.getCell(0).toString());

			// 2. 名称
			technicalField.setName(row.getCell(1).toString());

			// 3. 上级代码
			technicalField.setParent_code(row.getCell(2) == null ? "" : row.getCell(2).toString());

			// 4. 级别
			technicalField.setLevel(Double.valueOf(row.getCell(3).toString()).intValue());

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(technicalField);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 检测领域数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void DetectionFieldDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(detection_field);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		DetectionField detectionField;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			detectionField = new DetectionField();

			// 1. 类型
			detectionField.setType(row.getCell(0).toString());

			// 2. 代码
			detectionField.setCode(Double.valueOf(row.getCell(1).toString()).intValue());

			// 3. 名称
			detectionField.setName(row.getCell(2).toString());

			// 4. 上级代码
			detectionField.setParent_code(
					Double.valueOf(row.getCell(3) == null ? "0" : row.getCell(3).toString()).intValue());

			// 5. 级别
			detectionField.setLevel(Double.valueOf(row.getCell(4).toString()).intValue());

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(detectionField);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 龙江产业数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void LongjiangIndustryDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(longjiang_industry);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		LongjiangIndustry longjiangIndustry;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur == 1) {
				continue;
			}
			longjiangIndustry = new LongjiangIndustry();

			// 1. 类型
			longjiangIndustry.setType(row.getCell(0).toString());

			// 2. 代码
			longjiangIndustry.setCode(row.getCell(1).toString());

			// 3. 名称
			longjiangIndustry.setName(row.getCell(2).toString());

			// 4. 上级代码
			longjiangIndustry.setParent_code(row.getCell(3) == null ? "" : row.getCell(3).toString());

			// 5. 级别
			longjiangIndustry.setLevel(Double.valueOf(row.getCell(4).toString()).intValue());

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(longjiangIndustry);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 学科领域导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void SubjectFieldDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(subject_field);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(0);
		XSSFRow row;
		SubjectField subjectField;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur < 3) {
				continue;
			}
			subjectField = new SubjectField();

			subjectField.setCode(Double.valueOf(row.getCell(1).toString()).intValue());

			subjectField.setName(row.getCell(2).toString());

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(subjectField);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

	/**
	 * 国家数据导入
	 * 
	 * @throws Exception
	 */
	@Test
	public void CountryDataImport() throws Exception {
		InputStream ExcelFileToRead = new FileInputStream(subject_field);
		XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
		XSSFSheet sheet = wb.getSheetAt(2);
		XSSFRow row;
		Country country;
		Session session = null;
		long starTime = System.currentTimeMillis();

		Iterator<Row> rows = sheet.rowIterator();
		int cur = 0;
		while (rows.hasNext()) {
			row = (XSSFRow) rows.next();
			cur++;
			if (cur % 10 == 0)
				System.out.println(cur);
			if (cur < 3) {
				continue;
			}
			country = new Country();

			country.setName_simple(row.getCell(1).toString());

			country.setTwo_char(row.getCell(2).toString());

			country.setThree_char(row.getCell(3).toString());

			country.setNum_code(row.getCell(4).toString());

			country.setName_all(row.getCell(5).toString());

			// 存到mysql数据库中
			session = HibernateUtils.getCurrentSession();
			session.beginTransaction();
			session.save(country);
			session.getTransaction().commit();
		}
		wb.close();
		long endTime = System.currentTimeMillis();
		System.out.println("complete!\ntime:" + formatSumTime(endTime - starTime));
	}

}
